package com.wxm.killing.mq.bme;

import com.github.pagehelper.Page;
import com.wxm.killing.mq.common.enums.HttpStatusEnum;
import com.wxm.killing.mq.common.exception.BaseException;
import com.wxm.killing.mq.common.model.CommPager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Description TODO
 * @author: wxm
 * @date: 2019/10/21 21:23
 * @version: 1.0
 */
@Service
public class BmeService {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 获取列表
     *
     * @param tableName 表名
     * @param map       参数
     */
    public List<Map<String, Object>> list(String tableName, Map<String, Object> map) {
        tableName = tableName.trim();
        //存储参数变量
        List<Object> params = new ArrayList<>();
        //拼接条件语句
        String condition = BmeUtil.appendWhereCondition(map, params);
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT * FROM ").append(tableName).append(" WHERE 1=1 ").append(condition);
        List<Map<String, Object>> listMap = jdbcTemplate.queryForList(sql.toString(), params.toArray());
        return listMap;
    }

    /**
     * 查询列表
     *
     * @param sql
     * @param params
     * @return
     */
    public List<Map<String, Object>> list(String sql, List<Object> params) {
        return jdbcTemplate.queryForList(sql, params.toArray());
    }


    /**
     * 分页查询列表
     *
     * @param tableName
     * @param map
     * @return
     */
    public CommPager<List<Map<String, Object>>> pageList(String tableName, Map<String, Object> map) {
        //分页参数必须传递
        if (!map.containsKey(Constant.CURRENT_PAGE)) {
            throw new BaseException(HttpStatusEnum.PARAM_PAGE_ERROR);
        }
        if (!map.containsKey(Constant.PAGE_SIZE)) {
            throw new BaseException(HttpStatusEnum.PARAM_PAGE_ERROR);
        }
        int currentPage = BmeUtil.getCurrentPage(map);
        int pageSize = BmeUtil.getPageSize(map);
        tableName = tableName.trim();
        List<Object> params = new ArrayList<>();
        //拼接查询条件
        String condition = BmeUtil.appendWhereCondition(map, params);
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT * FROM ").append(tableName).append(" WHERE 1=1 ").append(condition);
        return pageList(currentPage, pageSize, sql.toString(), params);
    }

    /**
     * 分页查询列表
     * 分页查询1：查询记录总数   2：记录数>0 时查询当前页数据
     *
     * @param pageNumber
     * @param pageSize
     * @param sql
     * @param params
     * @return
     */
    public CommPager<List<Map<String, Object>>> pageList(int pageNumber, int pageSize, String sql, List<Object> params) {
        if (pageNumber < 1 || pageSize < 1) {
            throw new BaseException(HttpStatusEnum.PARAM_PAGE_ERROR);
        }
        //获取from部分
        sql = sql.toLowerCase();
        String sqlExceptSelect = sql.substring(sql.indexOf("from"));
        //查询记录总数的sql语句
        String totalRowSql = "select count(*) " + BmeUtil.replaceOrderBy(sqlExceptSelect);
        int totalRow = jdbcTemplate.queryForObject(totalRowSql, params.toArray(), Integer.class);
        //分页算法
        Page page = new Page(pageNumber, pageSize);
        page.setTotal(totalRow);
        if (totalRow == 0) {
            return new CommPager(page, new ArrayList());
        }
        String pageSql =sql+ " limit "+page.getStartRow()+", "+pageSize;
        List<Map<String, Object>> list = jdbcTemplate.queryForList(pageSql, params.toArray());
        return new CommPager(page, list);
    }

    /**
     * 插入
     *
     * @param tableName 表名
     * @param map       参数
     */
    public int add(String tableName, Map<String, Object> map) {
        List<Object> paras = new ArrayList<>();
        tableName = tableName.trim();
        //存放字段tablename括号内的值 (insert into tablename())
        StringBuffer fieldSql = new StringBuffer();
        //存放value  values后面值，暂时存放?  insert into tablename() values()
        StringBuffer valueSql = new StringBuffer();
        //拼接tablename()内字段  +values()字段  +参数
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            if (paras.size() > 0) {
                fieldSql.append(", ");
                valueSql.append(", ");
            }
            fieldSql.append('`').append(entry.getKey()).append('`');
            valueSql.append('?');
            paras.add(entry.getValue());
        }
        //拼接查询语句
        StringBuilder sql = new StringBuilder();
        sql.append("insert into `").append(tableName).append("`(").append(fieldSql).append(") values(").append(valueSql).append(")");
        return jdbcTemplate.update(sql.toString(), paras.toArray());
    }

    public int update(String tableName, Map<String, Object> map) {
        List<Object> params = new ArrayList<>();
        tableName = tableName.trim();
        StringBuilder setField = new StringBuilder();
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            String colName = entry.getKey();
            //判断是否是主键---主键不参与更新
            if (!"id".equals(colName)) {
                if (params.size() > 0) {
                    setField.append(", ");
                }
                setField.append('`').append(colName).append("` = ? ");
                params.add(entry.getValue());
            }
        }
        StringBuilder sql = new StringBuilder();
        sql.append("update `").append(tableName).append("` set ");
        sql.append(setField);
        sql.append(" where ");
        sql.append('`').append("id").append("` = ?");
        params.add(map.get("id"));
        return jdbcTemplate.update(sql.toString(), params.toArray());
    }

    public int update(String sql,List<Object> params) {
        return jdbcTemplate.update(sql, params.toArray());
    }

    public int delete(String tableName, String id) {
        tableName = tableName.trim();
        StringBuilder sql = new StringBuilder("delete from `").append(tableName).append("` where ");
        sql.append('`').append("id").append("` = ?");
        return jdbcTemplate.update(sql.toString(), id);
    }

    public int delete(String sql,List<Object> params) {
        return jdbcTemplate.update(sql, params.toArray());
    }

    public Map<String, Object> get(String tableName, String id) {
        tableName = tableName.trim();
        StringBuilder sql = new StringBuilder("select * from `").append(tableName).append("` where ");
        sql.append('`').append("id").append("` = ?");
        return jdbcTemplate.queryForMap(sql.toString(), id);
    }

    public String settings(String code){
        List<String> param = new ArrayList<>();
        param.add(code);
        String sql = "select value from tb_sys_config where code = ?";
        return jdbcTemplate.queryForObject(sql,param.toArray(),String.class);
    }

    /**
     * 统计记录数
     * @param tableName
     * @param map
     * @return
     */
    public int selectCount(String tableName,Map<String, Object> map){
        tableName = tableName.trim();
        //存储参数变量
        List<Object> params = new ArrayList<>();
        //拼接条件语句
        String condition = BmeUtil.appendWhereCondition(map, params);
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT count(*) FROM ").append(tableName).append(" WHERE 1=1 ").append(condition);
        return jdbcTemplate.queryForObject(sql.toString(),params.toArray(),int.class);
    }
}
